<?php
use think\Loader;
function exportExcel($expTitle,$expCellName,$expTableData,$fileName){
    $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
    $fileName = $fileName.date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
    $cellNum = count($expCellName);
    $dataNum = count($expTableData);
    vendor("PHPExcel.PHPExcel");
    Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
    $objPHPExcel = new PHPExcel();
    $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
    //设置单元格宽度
    //$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);

    foreach($expCellName as $key=>$val)
    {
        if($val[2] && $val[2]>0){
            $objPHPExcel->getActiveSheet()->getColumnDimension($cellName[$key])->setWidth($val[2]);
        }
    }
    //$objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
    for($i=0;$i<$cellNum;$i++){
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'1', $expCellName[$i][1]);
        setColor($objPHPExcel,$cellName[$i].'1','FF92CCDA');
    }
    for($i=0;$i<$dataNum;$i++){
        for($j=0;$j<$cellNum;$j++){
            $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+2), $expTableData[$i][$expCellName[$j][0]]);
            if($i%2==1){
                setColor($objPHPExcel,$cellName[$j].($i+2),'FFEEEEEE');
            }
        }
    }
    header('pragma:public');
    header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
    header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');  // 输出打印结果
    exit;
}
/**
 * 给导出的excel设置颜色
 * $objPHPExcel 对象  $xy 坐标 $color颜色，此颜色为16进制 前面一定要加FF
 **/
function setColor($objPHPExcel,$xy,$color)
{
    //border样式
    $styleThinBlackBorderOutline = array(
        'borders' => array (
            'outline' => array (
                'style' => PHPExcel_Style_Border::BORDER_THIN, //设置border样式
                'color' => array ('argb' => 'FF000000'), //设置border颜色
            ),
        ),);
    $objPHPExcel->getActiveSheet()->getStyle($xy)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    $objPHPExcel->getActiveSheet()->getStyle($xy)->getFill()->getStartColor()->setARGB($color);
    $objPHPExcel->getActiveSheet()->getStyle($xy)->applyFromArray($styleThinBlackBorderOutline);
}
/**
 * excel 导入
 * $file  excel文件
 *
 **/
function importExcel($filename,$type)
{
    vendor("PHPExcel.PHPExcel");
    if($type =="xls") {
        $objReader = PHPExcel_IOFactory::createReader('Excel5');
    }
    else if($type=="xlsx") {
        $objReader = PHPExcel_IOFactory::createReader('Excel2007');
    }
    $objReader->setReadDataOnly(true);
    $objPHPExcel = $objReader->load($filename);
    $objWorksheet = $objPHPExcel->getActiveSheet();
    $highestRow = $objWorksheet->getHighestRow();
    $highestColumn = $objWorksheet->getHighestColumn();
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
    $excelData = array();
    for ($row = 1; $row <= $highestRow; $row++) {
        for ($col = 0; $col < $highestColumnIndex; $col++) {
            $excelData[$row][] =(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();//拆分公司
        }
    }
    return $excelData;//返回excel解析数据
}
//下载文件
function download($path,$file_name)
{
    if (!file_exists($path))
    {
        echo "404";
        return false;
    }
    $userBrowser = $_SERVER['HTTP_USER_AGENT'];
    //处理Ie浏览器导致的文件名乱码
    if ( preg_match( '/MSIE/i', $userBrowser ) )
    {
        $file_name = urlencode($file_name);
    }
    $file_name = iconv('UTF-8', 'GBK//IGNORE', $file_name);
    $fp=fopen($path,'r');
    $file_size=filesize($path);
    Header("Content-type: application/octet-stream");
    Header("Accept-Ranges: bytes");
    Header("Accept-Length:".$file_size);
    Header("Content-Disposition: attachment; filename=".$file_name);
    $buffer=1024;
    $file_count=0;
    //向浏览器返回数据
    while(!feof($fp) && $file_count<$file_size){
        $file_con=fread($fp,$buffer);
        $file_count+=$buffer;
        echo $file_con;
    }
    fclose($fp);
}
